Imports System.IO
Imports System.Text.RegularExpressions
Imports System.Configuration.ConfigurationSettings
Imports System.Xml
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Data

Public Class Utilities


    ''' <summary>
    ''' Analyst Content file text and covert to ListItemData
    ''' </summary>
    ''' <param name="strContent">string content</param>
    ''' <param name="Delimiter">character delimiter</param>
    ''' <returns>List ListItemData</returns>
    ''' <remarks></remarks>
    Public Shared Function AnalystContent(ByVal strContent As String, ByVal Delimiter As Char) As List(Of ListItemData)
        Dim listItems As New List(Of ListItemData)
        Dim listTitle() As String
        Dim flag As Boolean = False
        Dim posTitle As Integer = 0
        Dim arrStr() As String
        Try
            arrStr = strContent.Split(Chr(13))
            For i As Integer = 0 To arrStr.Length - 1
                If flag = True Then
                    posTitle = 0
                    Dim listItem As New List(Of ItemData)
                    For Each value As String In arrStr(i).Split(Delimiter)
                        Dim idata As New ItemData(listTitle(posTitle), value)
                        listItem.Add(idata)
                        posTitle += 1
                    Next
                    listItems.Add(New ListItemData(i, listItem))
                Else
                    listTitle = arrStr(i).Split(Delimiter)
                    flag = True
                End If
            Next
            Return listItems
        Catch ex As Exception
            Dim filelog As String = writeToLogFile(ex.ToString(), "Error_AnalystContent")
            Return Nothing
        End Try


    End Function
    ''' <summary>
    ''' Validate data 
    ''' </summary>
    ''' <param name="emailAddress">String value</param>
    ''' <returns>Boolean</returns>
    ''' <remarks></remarks>
    Public Shared Function ValidateEmail(ByVal emailAddress As String) As Boolean
        Dim pattern As String = "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"
        Return Regex.IsMatch(emailAddress, pattern)
    End Function
    ''' <summary>
    ''' Write log file
    ''' </summary>
    ''' <param name="input">Content file</param>
    ''' <param name="preName">pre file name</param>
    ''' <remarks></remarks>
    Public Shared Function writeToLogFile(ByVal input As String, ByVal preFileName As String) As String
        Dim fileName As String
        fileName = Path.GetFullPath("LogFile") & "\" & preFileName & "_" & Now().Second & Now().Minute & Now().Hour & Now().Month & Now().Day & Now().Year & ".txt"
        Try
            If System.IO.File.Exists(fileName) = True Then
                'open file to write
                Dim objWriter As New System.IO.StreamWriter(fileName, True)
                objWriter.WriteLine(Now() & " - " & input)
                objWriter.Close()
            Else
                'create file 
                Dim TextFile As New StreamWriter(fileName)
                TextFile.WriteLine(input)
                TextFile.Close()
            End If
            Return fileName
        Catch ex As Exception
            Console.WriteLine(ex.Message & " can not write to log file")
            Return String.Empty
        End Try
    End Function
    ''' <summary>
    ''' Read file Xml
    ''' </summary>
    ''' <param name="fullPath">File Name</param>
    ''' <returns>ArrayList</returns>
    ''' <remarks></remarks>
    'Public Shared Function readFileXml(ByVal fullPath As String) As ArrayList
    '    Dim arrCondition As New ArrayList()
    '    Dim m_xmld As XmlDocument
    '    Dim m_nodelist As XmlNodeList
    '    Dim m_node As XmlNode
    '    'Create the XML Document
    '    Try

    '        m_xmld = New XmlDocument()
    '        'Load the Xml file

    '        m_xmld.Load(fullPath)
    '        'Get the list of name nodes 

    '        m_nodelist = m_xmld.SelectNodes("/regex/name")
    '        'Loop through the nodes

    '        For Each m_node In m_nodelist
    '            'Get the Gender Attribute Value

    '            Dim strColumName = m_node.Attributes.GetNamedItem("columName").Value
    '            'Get the firstName Element Value

    '            Dim conditionValidate = m_node.ChildNodes.Item(0).InnerText
    '            'Get the lastName Element Value

    '            Dim iValidate As New ItemValidate(strColumName, conditionValidate)
    '            arrCondition.Add(iValidate)

    '        Next
    '        Return arrCondition
    '    Catch errorVariable As Exception
    '        'Error trapping
    '        Dim filelog As String = writeToLogFile(errorVariable.ToString(), "Error_ReadFileXml")
    '        Return Nothing
    '    End Try

    'End Function
    ''' <summary>
    ''' Read file Excel
    ''' </summary>
    ''' <param name="fullPath">File name</param>
    ''' <returns>List listItemData</returns>
    ''' <remarks></remarks>
    Public Shared Function readFileExcel(ByVal fullPath As String, ByVal filename As String) As List(Of ListItemData)
        Dim listItems As New List(Of ListItemData)
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim dt As DataTable
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim valueItem As String = String.Empty
        Try
            If File.Exists(fullPath) Or File.Exists(fullPath & ".xls") Then
                MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; data source='" & fullPath & " '; " & "Extended Properties=Excel 12.0;")
                MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
                MyCommand.TableMappings.Add("Table", filename)

                DtSet = New System.Data.DataSet

                MyCommand.Fill(DtSet)

                dt = DtSet.Tables(0)

                Dim n As Integer = 0
                For Each dr As DataRow In dt.Rows
                    Dim listItem As New List(Of ItemData)
                    For i As Integer = 0 To dt.Columns.Count - 1
                        If dr(i).ToString().Trim = "" Then
                            valueItem = " "
                        Else
                            valueItem = dr(i).ToString()
                        End If
                        Dim idata As New ItemData(dt.Columns(i).ColumnName, valueItem)
                        listItem.Add(idata)
                    Next
                    listItems.Add(New ListItemData(n, listItem))
                    n += 1
                Next
                DtSet.Dispose()
                dt.Dispose()
                MyConnection.Close()
            Else
                Dim filelog As String = writeToLogFile("File is not exists.", "Error File.")
            End If
            Return listItems
        Catch ex As Exception
            Dim filelog As String = writeToLogFile(ex.ToString(), "Error_ReadFileExcel")
            Return Nothing
        End Try

    End Function

    ''' <summary>
    ''' Read file text
    ''' </summary>
    ''' <param name="FullPath">Full path </param>
    ''' <returns>Content file</returns>
    ''' <remarks></remarks>
    Public Shared Function ReadFileText(ByVal FullPath As String) As String
        Dim strContents As String = String.Empty
        Dim objReader As StreamReader
        Try
            objReader = New StreamReader(FullPath)
            strContents = objReader.ReadToEnd()
            objReader.Close()
            Return strContents
        Catch Ex As Exception
            Dim filelog As String = writeToLogFile(Ex.ToString(), "Error_ReadFileText")
            Return String.Empty
        End Try
    End Function


End Class
